USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetResellerAccountDetails')
	BEGIN
		DROP  Procedure  dbo.GetResellerAccountDetails
	END
GO

CREATE Procedure [dbo].[GetResellerAccountDetails]
	@PKID nvarchar(100) = NULL
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@states			varchar(max)
	,	@relations		varchar(max)

	select 
		@sql			= ''
	,	@states			= dbo.GetStatesXml()
	,	@relations		= dbo.GetRelationsXML(@PKID)

	;WITH PAcc AS (
		SELECT 
			p.PartnerNo			AS ResellerAccountNo
		,	p.PartnerName		AS ResellerAccountName
		,	p.Website			AS WebSite
		,	p.WebsiteDesc		AS WebsiteDesc
		,	p.PartnerGUID		AS ResellerAccountGUID	
		,	p.IsActive			AS IsActive
		,	p.PartnerDivision	AS PARTNERDIVISIONID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			CAST(p.PartnerGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			'0'		AS ResellerAccountNo
		,	''		AS ResellerAccountName
		,	''		AS WebSite
		,	''		AS WebsiteDesc
		,	NEWID()	AS ResellerAccountGUID	
		,	1		AS IsActive
		,	1		AS PARTNERDIVISIONID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			ISNULL(@PKID, '') = ''
	), PAccAddress AS (
		SELECT TOP 1
			a.AddressNo
		,	a.Street1
		,	a.Street2
		,	a.City
		,	a.State
		,	a.Zip
		,	a.AddressGUID
		,	p.ResellerAccountGUID
		FROM TR_ADDRESS as a WITH (NOLOCK)
		INNER JOIN PAcc as p WITH (NOLOCK)
			on p.ResellerAccountGUID = a.PartnerGUID
	), PAccComm AS (
		SELECT TOP 1
			c.CommunicationID
		,	c.Phone
		,	c.Fax
		,	a.AddressGUID
		FROM TR_COMMUNICATION as c WITH (NOLOCK)
		INNER JOIN PAccAddress as a
			on a.AddressGUID = c.AddressGUID
	), PAccNotes AS (
		SELECT TOP 1
			n.NoteID
		,	n.NoteText
		,	n.SourceGUID
		FROM TR_NOTE as n with (NOLOCK)
		INNER JOIN PAcc as p with (NOLOCK)
			on p.ResellerAccountGUID = n.SourceGUID
	), PAccDetail AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 1.1 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @states AS Details
		UNION
		SELECT 2.2 as ID, @relations AS Details
		UNION
		SELECT
			3 AS ID, 
			'<RESELLERACCOUNTNO><![CDATA[' + CAST(ResellerAccountNo AS VARCHAR) + ']]></RESELLERACCOUNTNO>'
		+	'<PKID><![CDATA[' + CAST(p.ResellerAccountGUID AS VARCHAR(40)) + ']]></PKID>'
		+	'<RESELLERACCOUNTGUID><![CDATA[' + CAST(p.ResellerAccountGUID AS VARCHAR(40)) + ']]></RESELLERACCOUNTGUID>'
		+	'<RESELLERACCOUNTNAME><![CDATA[' + ResellerAccountName + ']]></RESELLERACCOUNTNAME>'
		+	'<STREET1><![CDATA[' + ISNULL(Street1,'') + ']]></STREET1>'
		+	'<STREET2><![CDATA[' + ISNULL(Street2,'') + ']]></STREET2>'
		+	'<CITY><![CDATA[' + ISNULL(City,'') + ']]></CITY>'
		+	'<STATE><![CDATA[' + ISNULL([State], '') + ']]></STATE>'
		+	'<ZIP><![CDATA[' + ISNULL(Zip,'') + ']]></ZIP>'
		+	'<PHONE><![CDATA[' + ISNULL(Phone,'') + ']]></PHONE>'
		+	'<FAX><![CDATA[' + ISNULL(Fax,'') + ']]></FAX>'
		+	'<WEBSITE><![CDATA[' + ISNULL(WebSite,'') + ']]></WEBSITE>'
		+	'<WEBSITEDESC><![CDATA[' + ISNULL(WebSiteDesc,'') + ']]></WEBSITEDESC>'
		+	'<NOTEID><![CDATA[' + CAST(ISNULL(n.NoteID,0) AS VARCHAR) + ']]></NOTEID>'
		+	'<NOTES><![CDATA[' + ISNULL(n.NOTETEXT,'') + ']]></NOTES>'
		+	'<ISACTIVE><![CDATA[' + (CASE WHEN ISNULL(IsActive,1) = 1 THEN 'X' ELSE '' END) + ']]></ISACTIVE>' 
		+	'<ADDRESSNO><![CDATA[' + ISNULL(CAST(a.AddressNo AS VARCHAR),'0')+ ']]></ADDRESSNO>' 
		+	'<ADDRESSGUID><![CDATA[' + ISNULL(CAST(a.ADDRESSGUID AS VARCHAR(40)),'')+ ']]></ADDRESSGUID>' 
		+	'<COMMUNICATIONID><![CDATA[' + ISNULL(CAST(c.COMMUNICATIONID AS VARCHAR),'0') + ']]></COMMUNICATIONID>' 
		+	'<PARTNERDIVISIONID><![CDATA[' + ISNULL(CAST(p.PARTNERDIVISIONID AS VARCHAR),'0')+ ']]></PARTNERDIVISIONID>' 
					AS Details
		FROM PAcc as p
		LEFT OUTER JOIN PAccAddress as a
			ON a.ResellerAccountGUID = p.ResellerAccountGUID
		LEFT OUTER JOIN PAccComm as c
			on c.AddressGUID = a.AddressGUID
		LEFT OUTER JOIN PAccNotes as n
			on n.SourceGUID = p.ResellerAccountGUID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM PAccDetail
	Order by ID


	select @sql	AS Detail
END
